﻿-- =============================================
-- Author:		Rui Miguel Santos
-- Create date: 2011-11-20
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[proc_AnaliseCustoDia] 
	-- Add the parameters for the stored procedure here
	@IdUnidade bigint = null, 
	@Data Date 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	SELECT
	    c.IdCliente,
		c.Nome,
        u.IdUnidade,
        u.Designacao,
        CASE
			WHEN tt.TotalVendasEfectivo > 0 then
				tt.TotalCustosEfectivo / tt.TotalVendasEfectivo 
			ELSE
				0
			END AS RacioMprimasEfectivo,
		CASE
			WHEN tt.TotalVendasPrevisto > 0 THEN
				tt.TotalCustosPrevisto / tt.TotalVendasPrevisto 
			ELSE
				0
			END AS RacioMprimasPrevisto,
		tt.TotalCustosEfectivo,
		tt.TotalCustosPrevisto,
		tt.TotalVendasEfectivo,
		tt.TotalVendasPrevisto
	FROM 
		(SELECT c.IdUnidade,
			sum(isnull(r.TotalVendasPrevisto,0)) as TotalVendasPrevisto,
			sum(isnull(r.TotalCustosPrevisto,0)) as TotalCustosPrevisto,
			sum(isnull(r.TotalVendasEfectivo,0)) as TotalVendasEfectivo,
			sum(isnull(r.TotalCustosEfectivo,0)) as TotalCustosEfectivo
		 FROM refeicoes r
			inner join ContratosRefeicoes cr on cr.IdRefeicaoContratada = r.IdRefeicaoContratada
				inner join Contratos c on c.NumeroContrato = cr.NumeroContrato
		WHERE (r.DataRefeicao = @Data) 
		  AND (c.IdUnidade =@IdUnidade or @IdUnidade is null )
		GROUP BY c.IdUnidade) as tt 
	inner join Unidades u on u.IdUnidade = tt.IdUnidade
		inner join Clientes c on c.IdCliente = u.IdCliente
END